package com.mp3s.db_sqlite_pool.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mp3s.interface3.BackTestData;
import com.mp3s.interface3.QuoteData;
import com.mp3s.interface3.QuoteDataQueue;


public class sqliteImplpool {

    public void init() {

        
        String sql = "CREATE TABLE IF NOT EXISTS quote2 (" +
                    "stockId text NOT NULL, " +
                    "cnName text NOT NULL, " +
                    "open text NOT NULL, " +
                    "close text NOT NULL, " +
                    "high text NOT NULL, " +
                    "low text NOT NULL, " +
                    "lastClose text NOT NULL, " +
                    "volume text NOT NULL, " +
                    "date text NOT NULL)";

        try (Connection conn = SQLiteConnectionPool.getDataSource().getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.execute();
            System.out.println("Table created successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //backtest2
        sql = "CREATE TABLE IF NOT EXISTS backtest2 (" +
                "stockId text NOT NULL, " +
                "cnName text NOT NULL, " +
                "close text NOT NULL, " +
                "direct text NOT NULL, " +
                "date text NOT NULL)";

        try (Connection conn = SQLiteConnectionPool.getDataSource().getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.execute();
            System.out.println("Table created successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // //test
        // //backtest2
        // sql = "CREATE TABLE IF NOT EXISTS backtest2 (" +
        //         "stockId text NOT NULL, " +
        //         "cnName text NOT NULL, " +
        //         "close text NOT NULL, " +
        //         "direct text NOT NULL, " +
        //         "date text NOT NULL)";

        // try (Connection conn = SQLiteConnectionPool.getDataSource().getConnection();
        //     PreparedStatement pstmt = conn.prepareStatement(sql)) {
        //     pstmt.execute();
        //     System.out.println("Table created successfully.");
        // } catch (SQLException e) {
        //     e.printStackTrace();
        // }

    }

    public void insertOrupdate(List<QuoteData> qs){

        for(QuoteData q:qs){

            QuoteData q0 = findOne(q.getName(), q.getDate());
            if(q0.is_valid()){
                update(q);
            }else{
                insert(q);
            }
        }
    }
    
    public void insert(QuoteData q){
        String sql = "INSERT INTO quote2 VALUES (?, ?, ?, ?, ?, ?,?,?,?)";
        if(true){
            List<String>li = new ArrayList<>();
            li.add(q.getName());
            li.add(q.getCnName());
            li.add(q.getOpen());
            li.add(q.getClose());
            li.add(q.getHigh());
            li.add(q.getLow());
            li.add(q.getLastClose());
            li.add(q.getVolume());
            li.add(q.getDate());
            sql = replacePlaceholders(sql,li);
            WriteTaskQueue.addWriteTask(new QuoteDataQueue(q,sql));
            return;
        }

        //(2)
        int idx = 1;
        try (Connection connection = SQLiteConnectionPool.getDataSource().getConnection();
                PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(idx, q.getName());
            idx++;
            pstmt.setString(idx, q.getCnName());
            idx++;
            pstmt.setString(idx, q.getOpen());
            idx++;
            pstmt.setString(idx, q.getClose());
            idx++;
            pstmt.setString(idx, q.getHigh());
            idx++;
            pstmt.setString(idx, q.getLow());
            idx++;
            pstmt.setString(idx, q.getLastClose());
            idx++;
            pstmt.setString(idx, q.getVolume());
            idx++;
            pstmt.setString(idx, q.getDate());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        
    }

    public QuoteData findOne(String stockId,String date){
        
        QuoteData q = new QuoteData();
        String sql = "SELECT * FROM quote2 where date=\""+date+"\" and stockId=\""+stockId+"\"";
        try (Connection connection = SQLiteConnectionPool.getDataSource().getConnection();
                Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                String name = rs.getString("stockId");
                String cnName = rs.getString("cnName");
                String open = rs.getString("open");
                String close = rs.getString("close");
                String high = rs.getString("high");
                String low = rs.getString("low");
                String lastClose = rs.getString("lastClose");
                String volume = rs.getString("volume");
                String date0 = rs.getString("date");
                q.setName(name);
                q.setCnName(cnName);
                q.setOpen(open);
                q.setClose(close);
                q.setHigh(high);
                q.setLow(low);
                q.setLastClose(lastClose);
                q.setVolume(volume);
                q.setDate(date0);
                break;

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return q;
    }

    public List<QuoteData> findByStockId(String stockId){
    
        List<QuoteData> qs = new ArrayList<>();
        String sql = "SELECT * FROM quote2 where stockId=\""+stockId+"\"order by date asc";
        try (Connection connection = SQLiteConnectionPool.getDataSource().getConnection();
                Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                QuoteData q = new QuoteData();
                String name = rs.getString("stockId");
                String cnName = rs.getString("cnName");
                String open = rs.getString("open");
                String close = rs.getString("close");
                String high = rs.getString("high");
                String low = rs.getString("low");
                String lastClose = rs.getString("lastClose");
                String volume = rs.getString("volume");
                String date0 = rs.getString("date");
                q.setName(name);
                q.setCnName(cnName);
                q.setOpen(open);
                q.setClose(close);
                q.setHigh(high);
                q.setLow(low);
                q.setLastClose(lastClose);
                q.setVolume(volume);
                q.setDate(date0);
                qs.add(q);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return qs;
    }

    public void update(QuoteData q){
    
        String sql = "UPDATE quote2 SET close=?,high=?,low=?,volume=? WHERE date = \""+q.getDate()+"\" and stockId=\""+q.getName()+"\"";
        //(1)
        if(true){
            List<String>li = new ArrayList<>();
            li.add(q.getClose());
            li.add(q.getHigh());
            li.add(q.getLow());
            li.add(q.getVolume());
            sql = replacePlaceholders(sql,li);
            WriteTaskQueue.addWriteTask(new QuoteDataQueue(q,sql));
            return;  
        }
        //(2)
        int idx = 1;
        try (Connection connection = SQLiteConnectionPool.getDataSource().getConnection();
                PreparedStatement pstmt = connection.prepareStatement(sql)) {

            pstmt.setString(idx, q.getClose());
            idx++;
            pstmt.setString(idx, q.getHigh());
            idx++;
            pstmt.setString(idx, q.getLow());
            idx++;
            pstmt.setString(idx, q.getVolume());
            
            pstmt.executeUpdate();
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
    }

    public void del(){
    
        String sql = "DELETE FROM users WHERE name = ?";
        try (Connection connection = SQLiteConnectionPool.getDataSource().getConnection();
                PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, "John Doe");
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
    }

    //backest
    public void UpdateBackTest(QuoteData q,int direct){

        BackTestData q0 = findBackTestOne(q.getName(), q.getDate(),direct);
        if(q0.is_valid()){
            updateBT(q,direct);
        }else{
            insertBT(q,direct);
        }
        
    }

    
    public BackTestData findBackTestOne(String stockId,String date,int direct){
        
        BackTestData q = new BackTestData();
        String sql = "SELECT * FROM backtest2 where date=\""+date+"\" and stockId=\""+stockId+"\" and direct=\""+direct+"\"";
        try (Connection connection = SQLiteConnectionPool.getDataSource().getConnection();
                Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                String name = rs.getString("stockId");
                String cnName = rs.getString("cnName");
                String close = rs.getString("close");
                String idirect = rs.getString("direct");
                String date0 = rs.getString("date");
                q.setName(name);
                q.setCnName(cnName);
                q.setClose(close);
                q.setDirect(idirect);
                q.setDate(date0);
                break;

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        

        return q;
    }

    
    public void updateBT(QuoteData q,int direct){
        
        String sql = "UPDATE backtest2 SET close=?,direct=?  WHERE date = \""+q.getDate()+"\" and stockId=\""+q.getName()+"\"";
        
        //(1)
        if(true){
            List<String>li = new ArrayList<>();
            li.add(q.getClose());
            li.add(direct+"");
            sql = replacePlaceholders(sql,li);
            WriteTaskQueue.addWriteTask(new QuoteDataQueue(q,sql));
            return;
        }

        //(2)
        int idx = 1;
        try (Connection connection = SQLiteConnectionPool.getDataSource().getConnection();
                PreparedStatement pstmt = connection.prepareStatement(sql)) {

            pstmt.setString(idx, q.getClose());
            idx++;
            pstmt.setString(idx, direct+"");
            idx++;
            
            pstmt.executeUpdate();
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
    }

    
    public void insertBT(QuoteData q,int direct){
        
        String sql = "INSERT INTO backtest2 VALUES (?, ?, ?, ?, ?)";
        //(1)
        if(true){
            List<String>li = new ArrayList<>();
            li.add(q.getName());
            li.add(q.getCnName());
            li.add(q.getClose());
            li.add(direct+"");
            li.add(q.getDate());
            sql = replacePlaceholders(sql,li);
            WriteTaskQueue.addWriteTask(new QuoteDataQueue(q,sql));
            return;
        }
        //(2)
        int idx = 1;
        try (Connection connection = SQLiteConnectionPool.getDataSource().getConnection();
                PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(idx, q.getName());
            idx++;
            pstmt.setString(idx, q.getCnName());
            idx++;
            pstmt.setString(idx, q.getClose());
            idx++;
            pstmt.setString(idx, direct+"");
            idx++;
            pstmt.setString(idx, q.getDate());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    // 获得最近6个交易日数据
    public List<BackTestData> getBackTestData(){
        
        List<BackTestData> qs = new ArrayList<>();
        String sql = "WITH ms AS (\r\n" + //
                        "    SELECT\r\n" + //
                        "\t\tstockId,\r\n" + //
                        "        cnName,\r\n" + //
                        "        close,\r\n" + //
                        "\t\tdirect,\r\n" + //
                        "        date,\r\n" + //
                        "        ROW_NUMBER() OVER (PARTITION BY stockId ORDER BY date DESC) AS rn\r\n" + //
                        "    FROM\r\n" + //
                        "        backtest2\r\n" + //
                        ")\r\n" + //
                        "SELECT\r\n" + //
                        "\t\tstockId,\r\n" + //
                        "        cnName,\r\n" + //
                        "        close,\r\n" + //
                        "\t\tdirect,\r\n" + //
                        "        date\r\n" + //
                        "FROM\r\n" + //
                        "    ms\r\n" + //
                        "WHERE\r\n" + //
                        "    rn <= 6;";
        try (Connection connection = SQLiteConnectionPool.getDataSource().getConnection();
                Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                BackTestData q = new BackTestData();
                String name = rs.getString("stockId");
                String cnName = rs.getString("cnName");
                String close = rs.getString("close");
                String direct = rs.getString("direct");
                String date0 = rs.getString("date");
                q.setName(name);
                q.setCnName(cnName);
                q.setClose(close);
                q.setDirect(direct);
                q.setDate(date0);
                qs.add(q);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        

        return qs;
    }

    // 写操作
    public static void writeData(QuoteDataQueue value) {
        try (Connection connection = SQLiteConnectionPool.getDataSource().getConnection();
             PreparedStatement statement = connection.prepareStatement(value.getSQL())) {
            statement.executeUpdate();
        } catch (SQLException e) {
            System.out.println(value.getSQL());
            e.printStackTrace();
        }
    }

    /**
     * 将字符串中的占位符按顺序替换为 List 中的值
     *
     * @param template 包含占位符的字符串
     * @param values   需要替换的值列表
     * @return 替换后的字符串
     */
    public static String replacePlaceholders(String template, List<String> values) {
        // 检查占位符数量是否与 List 大小一致
        int placeholderCount = template.split("\\?").length - 1;
        if (placeholderCount != values.size()) {
            throw new IllegalArgumentException("占位符数量与值列表大小不匹配");
        }

        // 逐个替换占位符
        for (String value : values) {
            template = template.replaceFirst("\\?", "\'"+value+"\'");
        }

        return template;
    }

}


